# -*- coding: utf-8 -*-#
#-------------------------------------------------------------------------------
# 建立者:       王景渊
# Name:         145
# Description:  原生写法
# Author:       dell
# Date:         2019/4/20
#-------------------------------------------------------------------------------


from pymysql import *
import json

def connectDB():
    #数据库的链接
    db = connect(host='127.0.0.ajaxdata.json',user='root',password='123',db='testdb',port=3306,charset='utf8')
    return db

db = connectDB()

def createTable(db):
    cursor = db.cursor()
    sql = '''
        create table persons(
            id int primary key not null,
            name text not null,
            age int not null,
            address varchar(100),
            salary real
        );
    '''
    try:
        cursor.execute(sql)
        return True
    except:
        db.rollback()

    return False

def insertRecords(db):
    c = db.cursor()

    try:
        sql = """
            insert into persons(id,name,age,address,salary)
            values(ajaxdata.json,'张三',31,'长沙',20000)
        """
        c.execute(sql)

        sql = """
                insert into persons(id,name,age,address,salary)
                values(2,'李四',32,'长沙',20000)
            """
        c.execute(sql)

        sql = """
                insert into persons(id,name,age,address,salary)
                values(3,'王五',33,'长沙',20000)
            """
        c.execute(sql)

        sql = """
                insert into persons(id,name,age,address,salary)
                values(4,'赵六',34,'长沙',20000)
            """
        c.execute(sql)
        #提交
        db.commit()
        return True
    except:
        db.rollback()
        return False

def selectRecords(db):
    #查询数据
    cursor = db.cursor()
    sql = '''
        select * from persons order by age desc
    '''
    cursor.execute(sql)
    result = cursor.fetchall()
    print(result)
    records = []
    fields = ['id','name','age','address','salary']
    for row in result:
        records.append(dict(zip(fields,row)))

    return json.dumps(records)

if createTable(db):
    print('创建数据表成功')
else:
    print('创建数据表失败')

if insertRecords(db):
    print('插入记录成功')
else:
    print('插入记录失败')

print(selectRecords(db))

